GASで複数のPDFを結合する

Google Apps Script で複数のPDFを結合できないかと探していたら、officeの杜 | PDFを結合するという記事がありました。

officeの杜で共有されているこちらのコード にある mergePdfs() を活用させていただきました。元ネタは、stack overflowにある Merge Multiple PDF's into one PDF です。

結合できるPDFファイル

stack overflowのコメントにPDFのバージョンが1.5以上である場合には大きな修正が必要（結合できない）と記載されていますが、Googleの機能（印刷）やChromeの印刷でPDFにしたPDFはマージ可能でした。

対象	PDFバージョン	マージ可否
Googleドキュメント	1.4	◯
Googleプレゼンテーション	1.7	◯
Googleスプレットシート	1.7	◯
Google Chrome	1.4	◯
Office 365	1.7	✕

PDFのバージョンはAdobe Acrobat Readerで確認できます。

下記は、Googleドキュメントの印刷からPDFを保存した場合です。バージョンは1.4となっています。

Googleスプレッドシート、プレゼンテーションは、バージョンが1.7と表示されますが、

前述したように問題なく結合することができます。

Office 365でPDFに変換したファイルは結合できませんでしたが、Googleを中心としたサービスを利用しているのであれば問題なさそうです。

mergePdfs()関数を使う

mergePdfs()関数の説明と引数をみると

mergePdfs(directory, name, pdf1, pdf2, pdf3, ....)

drirectory	PDFが格納されているディレクトリID
name	結合したPDFのファイル名
pdf1, ...	結合するPDFファイル

結合するPDFファイルは2つ以上複数指定できますが、引数でファイル名をひとつづつ指定するのは煩わしい。

ということで、フォルダーにあるPDFをリスト（配列に格納）して引数として渡せるように変更してみました。

またPDFファイルの結合順番はファイル名で昇順にソートした順番にします。ファイル名の先頭に数字を入れておけば番号順で結合されます。

例えば、ファイル名を

0.test.pdf
1.test.pdf
2.test.pdf

としておけば、0.test、1.test、2.testの順番で結合されます。

mergePDFs()関数の修正

先ずは、office杜のこちらのリンクからmergePdfs()のプログラムを取得してください。

修正箇所は3箇所です。

12行目

関数の引数をファイル名から、配列を1つ渡すように変更します。

修正前：
function mergePdfs(directory, name, pdf1, pdf2, opt_pdf3) {

修正後：
function mergePdfs(directory, name, pdfList {

21行目

関数の引数ループで取得しているところを、引数に格納されているファイル数分（配列の長さ分）で処理するように変更します。

修正前：
for (var argumentIndex = 2; argumentIndex < arguments.length; argumentIndex++) {

修正後：
for (var i=0; i<pdfList.length; i++) {

23行目

マージするPDFのファイルサイズ取得を引数の配列からに変更します。

修正前：
var bytes = arguments[argumentIndex].getBlob().getBytes()

修正後：
var bytes = pdfList[i].getBlob().getBytes()

また、475行目移行はPDFを分割する関数のコードになっていますので削除してしまっても問題ありません。

mergePDFs()関数を呼び出す

次は、修正したmergePDFs()関数を呼び出すプログラムになります。指定したフォルダIDにあるPDFファイルを配列(pdfList)に格納して、ファイル名順にソートしてから mergePDFsに引き渡します。

<フォルダID>にはGoogleドライブのフォルダIDを、<ファイル名>には結合して作成するファイル名にしてください。

フォルダIDは、フォルダを開いたときのURL、https://drive.google.com/drive/folders/XXXXX のXXXX部分になります。

function merge(srcFolderId, fileName){
  var srcFolderId = '<フォルダID>' //フォルダ ID
  var fileName = '<ファイル名>' //結合後のファイル名

  //フォルダ内のファイルを取得
  var srcFolder = DriveApp.getFolderById(srcFolderId)
  var srcFiles = srcFolder.getFiles()

  //PDFファイルだけを配列に格納
  var pdfList = []
  while(srcFiles.hasNext()) {
    var srcFile = srcFiles.next()
    if (srcFile.getMimeType()==='application/pdf') {
      pdfList.push(srcFile);
    }
  }
  pdfList.sort()  //照準でソート
  mergePdfs(srcFolder, fileName, pdfList)
}

13行目のgetMimeType()でPDFファイルだけを対象にしています。また17行目のsort()で配列に格納した各ファイルをファイル名でソートしています。

スプレッドシートのUI機能で汎用的にする

もう少し汎用的に使えるように、スプレッドシートのUI機能を使って、フォルダIDと結合して作成するファイル名をUI上で入力するようにします。

またスプレッドシートのメニューからGASの実行を可能にします。

スプレッドシートの機能を使うので、Googleスプレッドシートの拡張機能から Apps Script を作成する必要があります。

スプレッドシートにメニューに追加する

スプレッドシートが開かれたときにメニューを追加します。こんな感じです。

「mergePDFs」 > 「PDFを結合」 で diaLog関数を実行します。

function onOpen(){ 
  SpreadsheetApp
    .getActiveSpreadsheet()
    .addMenu('mergePDFs', [
      {name: 'PDFを結合', functionName: 'diaLog'}
    ])
}

ダイアログ

詳細の説明は省きますが、「フォルダーID」と「ファイル名」の入力の後に確認画面を出してが簡単なチェックと確認を出力して merge()関数にフォルダIDとファイル名を引き渡します。

またPDFの結合でエラーとなった場合にもエラー出力をするようにしています。

下記がサンプルコードとなります。

function diaLog() {
/********************
PDFがあるフォルダを指定 
********************/
  var srcFolderId = Browser.inputBox("結合したいPDFがあるフォルダを指定してください（FolderID）",Browser.Buttons.OK_CANCEL)
  //キャンセルが押下されたら終了
  if (srcFolderId == 'cancel')  {
    return
  }
  try {
    var srcFolder = DriveApp.getFolderById(srcFolderId)
  }
  catch(e) {
    Browser.msgBox('ERROR', srcFolderId + ' is not available', Browser.Buttons.OK)
    return
  }
/********************
ファイル名の指定 
********************/
  var fileName = Browser.inputBox("結合後のファイル名を指定してください（FolderID）",Browser.Buttons.OK_CANCEL)
  //キャンセルが押下されたら終了
  if (fileName == 'cancel')  {
    return
  } else if (fileName == '') {
    Browser.msgBox('ERROR', 'File name is not defined', Browser.Buttons.OK)
    return
  }
/********************
開始の確認 
********************/
  var srcFolderName = srcFolder.getName()

  var result = Browser.msgBox('Confirm', '「' + srcFolderName + '」にあるすべてのPDFを\\n「' + fileName + '」として結合します', Browser.Buttons.OK_CANCEL)
  if (result == 'cancel')  {
    return
  }

  try {
    merge(srcFolderId, fileName)  //PDF格納フォルダ，ファイル名
  }
  catch(e) {
    Browser.msgBox('ERROR', 'Error Occurred during merging', Browser.Buttons.OK)
    return
  }
  
  Browser.msgBox('Complete', 'PDFの結合が完了しました', Browser.Buttons.OK)
}

これで完成です。

スプレッドシートのメニューから dialog()関数を呼び出し、merge()関数でフォルダ内のPDFをリスト化して、mergePDFs()関数でPDFを結合します。

先人の知恵と努力に感謝です。

GASの基礎を学べる参考図書

ある程度プログラミンがわかっていれば、WEBやYoutubeでも十分に調べられると思いますが、初歩的なところからであれば参考図書は有効な学習手段です。

「詳解! Google Apps Script完全入門 [第3版]」は、プラグラミング初心者にわかりやすく説明されています。GASを最初に学ぶ一冊として良書です。

詳解! Google Apps Script完全入門 [第3版]

高橋宣成 (著)

楽天ブックスで購入す

Amazonで購入

Udemy オススメ講座

【新IDE対応】Google Apps Script（GAS）の基礎を完全習得

講師：事務職たらこ

印象に残りやすい手書き風スライドを用いGASの基本的なプログラミングを気軽に学ぶことができる。本講座でGASを活用した自動化ができるレベルにはなれないが、基礎としては十分。

Googleスプレッドシートの更新された範囲をURLとして通知する

シートの更新通知を使うと確認が必要のない更新通知も送られてくるので、自分に必要な項目が更新されたときだけに通知が欲しい。

また、どこが変更されたのかが分かると、通知を受けた人に優しい仕様となります。

象と散歩: Googleスプレッドシートの更新された値を知るで、変更された箇所の行番号と列番号を取得する方法を紹介しましたが、変更範囲をセル範囲が指定されたリンクとして作成するGASプログラムを作ってみました。

URLの取得

onEdit()関数については、象と散歩: Googleスプレッドシートの更新された値を知る で説明していますが、スプレッドシートが更新されたときに実行される関数です。

onEdit(e)	{
  var ssUrl = e.source.getUrl()	//スプレッドシートのURL
  console.log(ssUrl)
}

セルの内容が更新されると、上記の関数が実行されます。マニュアルで実行するとエラーとなりますので、セルの値を更新してログを確認してください。

onEdit()関数の引数に e を指定していますが、e.source でスプレッドシートオブジェクトが取得でき、getUrl()でスプレッドシートのURLが取得できます。

シートはGIDパラメータで指定する

上記で取得したスプレッドシートのURLは、下記のような構成になっています。

https://docs.google.com/spreadsheets/d/xxxxx/edit

複数シートがある場合には、URLにGIDパラメータが付与されています。

https://docs.google.com/spreadsheets/d/xxxxx/edit#gid=749420395

最初のシート（シート 1）は、必ず gid=0 ですが、getSheetId() で GIDを取得することができます。

onEdit(e)	{
  var sheet = e.source.getActiveSheet()
  var sheetId = sheet.getSheetId()	//シートID
  console.log(sheetId)
} 

セル範囲はRANGEで指定する

セルを右クリックして"このセルへのリンクを取得"をクリックすると

下記のようなURLが取得できます。

https://docs.google.com/spreadsheets/d/xxxxx/edit#gid=0&range=C2

また複数セルを選択した状態で右クリックして"この範囲のリンクを取得"をクリックすると下記のようなURLが取得できます。

https://docs.google.com/spreadsheets/d/xxxxx/edit#gid=0&range=A3:C3

"range=" で範囲を指定したURLを取得できます。

A1形式で範囲を取得する

onEdit()で実行されたときにrangeで変更範囲が取得できますが、A3:C3のようなA1形式で範囲を取得するには、range.getA1Notation() を使います。

更新範囲がひとつのセルならA3、範囲ならA3:C3のように取得することができます。

onEdit(e)	{
  var sheetRange = e.range.getA1Notation()	//変更範囲
  console.log(sheetRange)
}

シートの値を変更して、ログを確認してください。複数セルの範囲は、シート上で範囲選択をしてコピペをすることで確認できます。

シートと範囲を指定したURLを作成

シートID（GID）と範囲（RANGE）を指定したURLを作成します。

function onEdit(e) {
  var ssUrl = e.source.getUrl()	//スプレッドシートのURL
  var sheet = e.source.getActiveSheet()
  var sheetId = sheet.getSheetId()	//シートID
  var sheetRange = e.range.getA1Notation()	//変更範囲
  var url = ssUrl + '#gid=' + sheetId + '&range=' + sheetRange
  console.log(url)
}

ログに出力されたURLをコピペして、範囲選択がされた状態でスプレッドシートが開くかを確認してください。

変更範囲の確認

特定の範囲が更新されたときだけ通知があるように、更新されたセルが対象としたいセル範囲内にあるかを確認します。

下記のサンプルプログラムでは、targetStartColumn, targetEndColumn で対象列を、targetStartRow, targetEndRow で対象行を指定しています。

下例では、C列の2行目から1000行目が更新されたかを確認するサンプルコードとなります。

function onEdit(e)  {
  var targetStarColumn =3 //対象セル（開始列）
  var targetEndColumn = 3 //対象セル（終了列） 
  var targetStartRow = 2  //対象セル（開始列）
  var targetEndRow = 1000 //対象セル（終了列）

  var rowStart = e.range.rowStart //更新開始行の取得
  var rowEnd = e.range.rowStart   //更新終了行の取得
  var columnStart = e.range.columnStart //更新開始列の取得
  var columnEnd = e.range.columnEnd     //更新終了列の取得

  var ssUrl = e.source.getUrl() //スプレッドシートのURL
  var sheet = e.source.getActiveSheet()
  var sheetId = sheet.getSheetId()  //シートID
  var sheetRange = e.range.getA1Notation()  //変更範囲

  var text = '変更箇所の通知\n'
  if (targetStarColumn <= columnStart &&  columnEnd <= targetEndColumn
      &&  targetStartRow <= rowStart &&  rowEnd <= targetEndRow) {
    text = text + ssUrl + '#gid=' + sheetId + '&range=' + sheetRange
  } else  {
    text = '対象範囲外の更新'
  }
  console.log(text)
}

対象範囲が更新された場合に "更新されたシートとセル範囲を指定したURL" を作成します。シートを更新して実行結果をログで確認してください。

メールを送信する

シンプルトリガーのonEdit()ではメール送信ができないので、function名を変更して、トリガーを設定します。

トリガーの設定方法については、

• 象と散歩: Googleスプレッドシートの更新された値を知る
• 象と散歩: GASでGoogleフォームの値を取得する（トリガー編）

を参考にしてください。

メール送信は GmailAppクラスのsendEmailメソッドを使います。

GmailApp.sendEmail(宛先, 件名, 本文, {オプション})

オプションは省略可能です。（詳細はClass GmailAppを参照してください）

mailTo で指定しているメールアドレスは、自分のメアドに変更してください。

function updateNotice(e) {
  var ssUrl = e.source.getUrl()	//スプレッドシートのURL
  var sheet = e.source.getActiveSheet()
  var sheetId = sheet.getSheetId()	//シートID
  var sheetRange = e.range.getA1Notation()	//変更範囲
  var url = ssUrl + '#gid=' + sheetId + '&range=' + sheetRange

  // メール送信
  var mailTo   = 'sample@gmail.com'
  var subject  = '更新通知'
  var bodyText = '変更箇所の通知\n'
  bodyText     = bodyText + url
	
  GmailApp.sendEmail(
    mailTo,
    subject,
    bodyText
  )
}

特定のセルが更新されたときにメールを出す場合には下例を参考にしてください。

function updateNotice(e) {
  var targetStarColumn = 3	//対象セル（開始列）
  var targetEndColumn = 3	//対象セル（終了列） 
  var targetStartRow = 2	//対象セル（開始列）
  var targetEndRow = 1000	//対象セル（終了列）

  var rowStart = e.range.rowStart //更新開始行の取得
  var rowEnd = e.range.rowStart   //更新終了行の取得
  var columnStart = e.range.columnStart //更新開始列の取得
  var columnEnd = e.range.columnEnd     //更新終了列の取得

  var ssUrl = e.source.getUrl() //スプレッドシートのURL
  var sheet = e.source.getActiveSheet()
  var sheetId = sheet.getSheetId()  //シートID
  var sheetRange = e.range.getA1Notation()  //変更範囲

  var text = '変更箇所の通知\n'
  if (targetStarColumn <= columnStart &&  columnEnd <= targetEndColumn
      &&  targetStartRow <= rowStart &&  rowEnd <= targetEndRow) {
    text = text + ssUrl + '#gid=' + sheetId + '&range=' + sheetRange
    sendMail(text)
  }
}
function sendMail(text)	{
  // メール送信
  var mailTo   = 'sample@gmail.com'
  var subject  = '更新通知'
  var bodyText = text

  GmailApp.sendEmail(
    mailTo,
    subject,
    bodyText
  )
}

上記例では更新されたセル範囲でURLを作成していますが、range=開始行番号:終了行番号とすると、更新された行をフォーカスしたURLが作成できます。

https://docs.google.com/spreadsheets/d/XXXXX/edit#gid=0&range=3:3

上記も、rowStart と rowEnd を使って簡単に作成できます。

GASの基礎を学べる参考図書

ある程度プログラミンがわかっていれば、WEBやYoutubeでも十分に調べられると思いますが、初歩的なところからであれば参考図書は有効な学習手段です。

「詳解! Google Apps Script完全入門 [第3版]」は、プラグラミング初心者にわかりやすく説明されています。GASを最初に学ぶ一冊として良書です。

詳解! Google Apps Script完全入門 [第3版]

高橋宣成 (著)

楽天ブックスで購入す

Amazonで購入

Udemy オススメ講座

【新IDE対応】Google Apps Script（GAS）の基礎を完全習得

講師：事務職たらこ

印象に残りやすい手書き風スライドを用いGASの基本的なプログラミングを気軽に学ぶことができる。本講座でGASを活用した自動化ができるレベルにはなれないが、基礎としては十分。

GASでExcelをGoogleスプレッドシートに変換する

ExcelファイルのデータをGASで処理するには、GoogleDrive上でExcelファイルを開いてGoogleスプレッドシートで保存する必要があります。

小さなExcelファイルなら上記の方法でも構いませんが、大きなExcelファイルだと ”Googleスプレッドシートとして保存” で時間がかかりすぎて保存できない、、、。

また複数ファイルがあるときは一括変換できれば楽ちんです。

ということで、GASでExcelファイルをGoogleスプレッドシートに変換する２つの方法について説明します。

1. Excelファイルを指定してスプレッドシートに変換する
2. フォルダにあるExcelファイルを一括変換する

Excel to Google Sheets には Drive APIが必要

Google Apps Script でExcelファイルを開く場合には、Googleドライブのファイルやフォルダを操作できる DriveAppクラス を使います。

但し、そのまま、DriveAppのコードを書いて実行しても

エラー ReferenceError: Drive is not defined

と、実行できません。

Drive APIサービスを追加する

DriveAppクラスを使うためには、Apps Scriptエディターの左側のメニューにある「サービス」からDrive APIを追加する必要があります。

① サービスの右側にある＋をクリック

② 一覧から Drive API を選択して追加

Drive APIが追加されると左メニューのサービスの下にDriveが表示されます。

これで DriveAppクラスが利用できます。

Excelファイルを指定してスプレッドシートに変換する

Excelファイルを指定して、指定したフォルダにGoogleスプレッドシートを出力するサンプルコードとなります。

Excelファイルと出力先のフォルダはIDで指定します。

ExcelファイルとフォルダのID

GoogleドライブからExcelファイルを開いたURLのXXXXXの部分がIDとなります。

https://docs.google.com/spreadsheets/d/XXXXX/edit

GoogleドライブのフォルダIDは、XXXXXの部分がIDになります。

https://docs.google.com/drive/u/0/folder/XXXXX

サンプルコード（Excelファイル指定）

上記で説明したExcelファイルと出力先のフォルダのIDを指定する、簡単なサンプルコードです。

12行目で取得したExcelファイル名を13行目でログに出力していますが、変換したGoogleスプレッドシート名も同じファイル名にしています。

function convertExcel()  {
  //変換するExcelファイル
  let excelFileId = 'XXXXX'

  //スプレッドシート出力先フォルダー
  let destFolderId = 'XXXXX'

  //ExcelファイルをファイルIDで取得
  var excelFile = DriveApp.getFileById(excelFileId)

  //Excelファイル名取得
  var fileName = excelFile.getName()
  Logger.log(fileName)

  var option =  {
    mimeType:MimeType.GOOGLE_SHEETS,   //Google sheets
    parents:[{id:destFolderId}],                       //出力先フォルダー
    title:fileName                                          //出力先ファイル名
  }
  Drive.Files.insert(option,excelFile)
}

20行目の

Drive.Files.insert(option,excelFile)

で、変換を実施していますが、15〜19行目のオプションで、

• MIMEタイプ
• 出力先フォルダー
• 出力先ファイル名

を指定しています。

スプレッドシートへの変換なのでMIMEタイプをGOOGLE_SHEETSとしていますが、Enum MimeType | Apps Script | Google Developers に全てのMIMEタイプが記載されています。

変換後に処理を続ける

Googleスプレッドシートに変換してから、そのままシートをGAS処理する場合には、20行目からを下記のように変更してください。

取得したシート名をログに出力する簡単なサンプルです。

function convertExcel()  {
  //変換するExcelファイル
  let excelFileId = 'XXXXX'

  //スプレッドシート出力先フォルダー
  let destFolderId = 'XXXXX'

  //ExcelファイルをファイルIDで取得
  var excelFile = DriveApp.getFileById(excelFileId)

  //Excelファイル名取得
  var fileName = excelFile.getName()
  Logger.log(fileName)

  var option =  {
    mimeType:MimeType.GOOGLE_SHEETS,   //Google sheets
    parents:[{id:destFolderId}],                       //出力先フォルダー
    title:fileName                                          //出力先ファイル名
  }
  var sheetObj = Drive.Files.insert(option,excelFile)

  //スプレッドシートIDを使って、スプレッドシートオブジェクトを取得
  var sheet = SpreadsheetApp.openById(sheetObj.id).getActiveSheet()
  Logger.log(sheet.getName())  
}

変換したスプレッドシートにシートが複数ある場合には、getSheetByName() でシート名を指定します。

フォルダにあるExcelファイルを一括変換する

次は、フォルダにある複数のExcelファイルを一括でGoogleスプレッドシートに変換する方法です。

フォルダにExcelファイル以外がある場合には処理されないようにしています。

function convertExcels() {
  //Excelがあるフォルダ
  let sourceFolderId = 'XXXXX'
  //スプレッドシート出力先フォルダー
  let destFolderId = 'XXXXX'

  // フォルダ配下のファイルを取得
  var sourceFolder = DriveApp.getFolderById(sourceFolderId)
  var folderFiles  = sourceFolder.getFiles()

  // Excelファイルをイテレートして順にスプレッドシートに変換
  while(folderFiles.hasNext()) {
    var file = folderFiles.next()
    //Excelファイルの判断   
    if (file.getMimeType() == 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')  {
      var fileName  = file.getName()
      Logger.log(fileName)
      Logger.log(file.getMimeType())
      var option =  {
        mimeType:MimeType.GOOGLE_SHEETS,  //Google sheets
        parents:[{id:destFolderId}],      //出力先フォルダーを指定
        title:fileName                    //出力先ファイル名
      }
      Drive.Files.insert(option,file)
    }
  }
}

フォルダにあるファイル一覧を取得しているのが、8〜9行目です。

そして、12行目の while文のhasNext() で13行目の next()で取得したファイル一覧から取り出せるファイルが残っているかを調べて、順番にファイルを処理していきます。

Excelファイルだけを変換するために、取り出したファイルがEXCELファイルかを15行目で調べています。

18行目でgetMimeType()でファイルのMIMEタイプを出力していますが、application/vnd.openxmlformats-officedocument.spreadsheetml.sheet が、拡張子xlsのExcelファイルとなります。

GASの基礎を学べる参考図書

ある程度プログラミンがわかっていれば、WEBやYoutubeでも十分に調べられると思いますが、初歩的なところからであれば参考図書は有効な学習手段です。

「詳解! Google Apps Script完全入門 [第3版]」は、プラグラミング初心者にわかりやすく説明されています。GASを最初に学ぶ一冊として良書です。

詳解! Google Apps Script完全入門 [第3版]

高橋宣成 (著)

楽天ブックスで購入す

Amazonで購入

Udemy オススメ講座

【新IDE対応】Google Apps Script（GAS）の基礎を完全習得

講師：事務職たらこ

印象に残りやすい手書き風スライドを用いGASの基本的なプログラミングを気軽に学ぶことができる。本講座でGASを活用した自動化ができるレベルにはなれないが、基礎としては十分。

VLOOKUPを使わない方法（INDIRECT＋MATCH）

GoogleスプレッドシートやEXCELで、他の表とキーが一致する行の値を取得するときにはVLOOKUP関数を使うのが定石です。

VLOOKUPはとっても便利な関数なんですが、不便なところもあります。

• 検索できるのは指定した範囲の先頭列のみ
• 取得する列は列番号で指定する

一番の問題は、VLOOKUP関数で検索できるのは先頭列のみで、取得する値がある列は検索する列よりも右側になければなりません。

もちろん表の列を移動させれば解決できますが、Google Sheetsで IMORTRANGE関数 で他のスプレッドシートを読み込んでいる場合などは致命的です。

また取得する値を列番号で指定するのも、AD列なら30と指定しなければならないので、列数が多くなるとわからなくなってしまいます。

ということで、VLOOKUP の代わりとなる方法（関数）の紹介です。

サンプルで使用するデータ

サンプルの表は「国番号一覧」と「国コード一覧」２つのシートです。

国番号一覧に2桁の国コードを取得するために、「国番号一覧」シートの「国名（日本語）」で「国コード一覧」シートの「日本語名」を検索して国名が一致する「２文字」から2桁の国コードを取得します。

下記の2表をコピペしてシートに貼り付けてください。

国番号一覧

国番号(電話)	エリア	国名(日本語)	国名(英語)	国コード
60	アジア	マレーシア	Malaysia
61	太平洋諸国	オーストラリア	Australia
62	アジア	インドネシア共和国	Indonesia
63	アジア	フィリピン共和国	Philippines
64	太平洋諸国	ニュージーランド	New Zealand
65	アジア	シンガポール共和国	Singapore
66	アジア	タイ王国	Thailand
81	アジア	日本国	Japan
82	アジア	大韓民国	Republic of Korea
84	アジア	ベトナム社会主義共和国	Viet Nam
86	アジア	中華人民共和国	Chaina
90	アジア	トルコ共和国	Turkey
91	アジア	インド	India
92	アジア	パキスタン・イスラム共和国	Pkistan
93	アジア	アフガニスタン・イスラム共和国	Afghanistan
94	アジア	スリランカ民主社会主義共和国	Srilanka
95	アジア	ミャンマー連邦	Myanmar
98	アジア	イラン・イスラム共和国	Iran

国コード一覧

2文字	3文字	英語名	日本語名
AF	AFG	Afghanistan	アフガニスタン・イスラム共和国
AU	AUS	Australia	オーストラリア
CN	CHN	China	中華人民共和国
IN	IND	India	インド
ID	IDN	Indonesia	インドネシア共和国
IR	IRN	Iran	イラン・イスラム共和国
JP	JPN	Japan	日本国
KR	KOR	Republic of Korea	大韓民国
MY	MYS	Malaysia	マレーシア
MM	MMR	Myanmar	ミャンマー連邦
NZ	NZL	New Zealand	ニュージーランド
PK	PAK	Pakistan	パキスタン・イスラム共和国
PH	PHL	Philippines	フィリピン共和国
SG	SGP	Singapore	シンガポール共和国
LK	LKA	Sri Lanka	スリランカ民主社会主義共和国
TH	THA	Thailand	タイ王国
TR	TUR	Turkey	トルコ共和国
VN	VNM	Viet Nam	ベトナム社会主義共和国

EXCELならXLOOKUPを使う

Microsoft365（EXCEL）であれば VLOOKUP の上位互換である XLOOKUP があります。XLOOKUP関数を使えば、VLOOKUP の不便さが解消できます。

XLOOKUP 関数 に詳細の説明がありますが、今回のケースであれば、とってもシンプルに記載できます。

上図の国番号一覧シートの ”E2” に下記のように記載します。

=XLOOKUP(C2, 国コード一覧!D:D, 国コード一覧!A:A)

上式で国番号一覧シートのC2（国名=マレーシア）で、国コード一覧のD列（日本語名）を検索して、国名が一致する国コード一覧のA列の値が取得できます。

検索する値（C2）、検索する列（国コード一覧!D:D）、取得する値の列（国コード一覧!A:A）と、理解しやすい表記ができます。

国コード一覧シートは下記のようになっています。

優れもののXLOOKUPですが、残念ながらMicrosoft365（旧Office365）でしか使えません。

Excel2019/2016では、後述するGoogleスプレッドシートで説明する方法を使ってください。

Google Sheets なら関数を組み合わせる

GoogleスプレッドシートにはXLOOKUP関数に対応する関数はありません。

しかし、関数を組み合わせることによってVOOKUPより便利にキーが一致する別表の値を取得することができます。

INDEX関数＋MATCH関数

INDEX関数とMATCH関数の組み合わせる方法については、INDEX - ドキュメント エディタ ヘルプ にも記載されています。

VLOOKUPではできなかった検索する列が参照範囲の左端（1列目）になくても検索が可能になります。しかし、キーが一致する行の値を取得する列については列番号（数字）で指定する必要があります。

上図の国番号一覧シートの ”E2” に下記のように記載します。

=INDEX('国コード一覧'!A:D, MATCH(C2, '国コード一覧'!D:D,0), 1)

先ずは、INDEX関数 の説明です。

=INDEX（参照範囲, 行番号, 列番号）

上の式を分解すると、

参照範囲	'国コード一覧'!A:D
行番号	MATCH(C2,'国コード一覧'!D:D,0)
列番号	1

となります。

行番号で指定しているMATC関数については後述しますが、'国コード一覧'!A:Dの中から1列目つのデータ（A列「2文字」の国コード）の値を取得するということを意味します。

続いて MATCH関数 の部分です。

構文は、

=MATCH(検索キー, 検索範囲, 検索種類)

です。検索キーで検索範囲の中で一致するものを探します。

MATCH(C2, '国コード一覧'!D:D,0)を分解すると、

検索キー	C2	マレーシア
検索範囲	国コード一覧'!D:D	国コード一覧のD列
検索種類	0	完全一致

国コードシートのD列から「マレーシア」と完全一致するものを探してきます。戻り値は、一致するものが何番目に見つかったです。

=MATCH(C2, '国コード一覧'!D:D, 0)

とすると、D1から10個目で「マレーシア」と一致するので戻り値は１０となります。

検索範囲は、'国コード一覧'!D:Dの部分を、D1:D19 としても戻り値は ”10” で表の行番号と一致します。

しかし、データが入っているのがD2からだといって

=MATCH(C2, '国コード一覧'!D2:D19, 0)

とすると、D2からマレーシアは9番目となるので戻り値が “9” となってしまいます。これだと行番号と一致しなくなってしまうので、INDEX関数で違う場所を参照してしまいます。

行番号は指定せずに D:D のように検索範囲を列だけにすれば必ず行番号と一致します。

検索種類は、完全一致である 0 を必ず指定してください。省略してしまうと正しい答えが求められません。詳細は INDEXのヘルプ を参照してください。

INDIRECT関数＋MATCH関数

INDEX＋MATCH関数では、値を取得する列を列番号で指定しなければなりませんが、INDIRECT関数を使うと文字列として列を指定することができます。

百聞は一見に如かずです。

=INDIRECT("A" & 10)

これで、A10セルの値を取得できます。

INDIRECT関数を使って、国番号一覧シートの ”E2” に下記のように記載します。

列番号は、上で説明したMATCH関数で取得します。

=INDIRECT("'国コード一覧'!A" & MATCH(C2,'国コード一覧'!D:D,0))

このように書くと、国コード一覧シートのD列の中でC2と完全一致する行の A列の値を取得することができます。

INDIRECTを分解すると下記のようになります。

行名	"'国コード一覧'!A"	文字列として指定
結合記号	&
列番行	MATCH(C2,'国コード一覧'!D:D,0)	戻り値は列番号

国コード一覧のA列という指定は文字列なので、国コード一覧シートで列が変更されると式も変更しなければなりません。

それは、INDEX+MATCH関数の列番号でも同じなので、INDIRECTを使った方が、視覚的に理解しやすい式となります。

VLOOKを使わずにINDIRECT関数とMATCH関数を使ってみてはいかがでしょうか。

Googleドキュメントを使ってGASで差し込みメールを出す（HTMLメールも可）

GASでメールを送信をするのは簡単ですが、メール本文を整形するのが面倒です。

しかし、Googleドキュメントでメールのテンプレートを作れば簡単です。またメール本文の一部を変数として、送付する人の名前などを書き換える差し込み機能を使うこともできます。

テキストメールだけでなくHTMLメールでも同じようにGoogleドキュメントでテンプレートを作成して差し込みメールを出すことも可能です。

今回説明するのは、

• Googleドキュメントでメールのテンプレートを作る
• メール本文の一部を送信する人によって書き換える
• HTMLメールの送信

についてです。

実際に送付されるHTMLメールはこんな感じです。赤字にしている部分が差し込みです。画像も添付しています。

テキストメールのサンプルは下記となります。テキストメールなので画像はなく、リンクも使えないのでURLの記載となります。

メールのテンプレート作成する

先ずは、Googleドキュメントでメールのテンプレートを作成します。

テキストメールのテンプレート

テキストメールは、見たまんまなのでとても簡単です。

赤字が差し込み用の変数です。

面談日のお知らせ

[value1]さん、面談の日程が確定しました。

日付　：[value2]

時間　：[value3]

詳細については、下記URLからご確認ください。

https://walking-elephant.blogspot.com/2021/05/shower-head.html

差し込みとするのが赤字の部分です。プログラムで判断できる文字列であれば何でも構いませんが、

• [value1]　・・・　氏名
• [value2]　・・・　日付
• [value3]　・・・　時間

としています。

HTMLメールのテンプレート

続いて、HTMLメールのテンプレートとなります。橙色がHTMLタグです。

<h2>面談日のお知らせ<h2>

<p><strong>[value1]</strong>さん、面談の日程が確定しました。</p>

<ui>

    <li>日付　：<span style="color: red">[value2]</span></li>

    <li>時間　：<span style="color: red">[value3]</span></li>

</ui>

 

<p>詳細については、<a href="https://walking-elephant.blogspot.com/2021/05/shower-head.html">こちら</a> からご確認ください。</p>

<img alt="ゾウでもわかる Google Apps Script" height="180px;" src="https://lh3.googleusercontent.com/StoB14GWX9gSlf6HgsiRuWEn9TyN7XIejqUeWh9GCxQmrGVVxMwXE19xThkj_VvgVOIAdF_3iACbSJyJYPG1hro-bnl9G7yEqMoCWHxgP43oCe-0RHSMTt3fDjoOcLKVSkGbeerL9vI=s16000" title="ゾウでもわかる Google Apps Script" width="320px;" />

GASでメールを送信する

サンプルプログラムは、下記の３パートから構成されています。

1. Googleドキュメントを読み込む
2. 読み込んだコンテンツから特定文字列を置換する
3. HTMLメールを送信する

HTMLメールで送信しますが、HTMLメールが受信できない環境であればテキストメールが表示されます。

サンプルコードは下記になります。

function testMail() {
/***************************************************
* Googleドキュメントを読み込む
***************************************************/
  //GoogleドキュメントのID
  var textDocId = '...1234567890...'
  var htmlDocId = '...abcdefghij...'

  //テキストメール テンプレートの取得
  var txtDoc = DocumentApp.openById(textDocId)
  var txtTemplate = txtDoc.getBody().getText()

  //HTMLメール テンプレートの取得  
  var htmlDoc = DocumentApp.openById(htmlDocId)
  var htmlTemplate = htmlDoc.getBody().getText()

/***************************************************
* メール本文の特定文字列を置換する
***************************************************/
  //変数の設定
  var var1 = 'タカハシ'
  var var2 = '2012年5月14日（金）'
  var var3 = '13:00'

  //プレーンテキスト内の変数を置換
  var bodyText = txtTemplate.replace('[value1]',var1).replace('[value2]',var2).replace('[value3]',var3)

  //HTML内の変数を置換
  var bodyHtml = htmlTemplate.replace('[value1]',var1).replace('[value2]',var2).replace('[value3]',var3)
  
/***************************************************
* HTMLメールを送信する
***************************************************/
  var mailTo = 'sample_mail@gmail.com'
  var subject = 'テストメール'

  GmailApp.sendEmail(
    mailTo,
    subject,
    bodyText,
    {
      htmlBody:bodyHtml
    }
  )
}

Googleドキュメントを読み込む

Googleドキュメントで作成したテンプレートをIDを指定して読み込みますが、６行目がテキストメール、７行目がHTMLメールのGoogleドキュメントのIDとなります。

5. //GoogleドキュメントのID
6. var textDocId = '...1234567890...'
7. var htmlDocId = '...abcdefghij...'

Googleドキュメントの編集URLが下記の場合、赤字の部分がIDです。

https://docs.google.com/document/d/...1234567890.../edit

Googleドキュメントで作成したテンプレートの値に変更してください。

10行目でGoogleドキュメントで作成したテキストメールのテンプレートを上記のIDを指定して開いて、11行目で内容をテキストとして変数 txtTemplate に代入しています。

  9. //テキストメール テンプレートの取得
10. var txtDoc = DocumentApp.openById(textDocId)
11. var txtTemplate = txtDoc.getBody().getText()

HTMLメールのテンプレートも同じように14-15行目で変数 htmlTemplate に代入します。

これで、Googleドキュメントの読み込みは完了です。

メール本文の特定文字列を置換する

差し込み部分の説明です。

21-23行目でメールに差し込む値を定義しています。

20. //変数の設定
21. var var1 = 'タカハシ'
22. var var2 = '2012年5月14日（金）'
23. var var3 = '13:00'

26行目がテキストメール・テンプレートから読み込んだ値の置換、29行目がHTMLメール・テンプレートから読み込んだ値の置換です。

25. //プレーンテキスト内の変数を置換
26. var bodyText = txtTemplate.replace('[value1]',var1),.....省略.....
27.
28. //HTML内の変数を置換
29. var bodyHtml = htmlTemplate.replace('[value2]',var1)......省略.....

replaceを使って文字列を置換しています。

Strings.replace('置換前の文字列','置換後の文字列')

変数をひとつにして説明すると、下記の例では、txtTemplate にある [value1] を探して 細野 に置換した内容を bodyText に代入しています。

var bodyText = txtTemplate.replace('[value1]','細野')

差し込みが多い場合には下記のように分割することもできます。

var bodyText = txtTemplate.replace('[value1]',var1)
var bodyText = bodyText.replace('[value2]',var2)
var bodyText = bodyText.replace('[value3]',var3)

HTMLメールを送信する

メールを送信するのには GmailApp.sendEmail を使います。

GmailApp.sendEmail(送付先メアド, メールタイトル, 本文, {オプション})

34-35行目で mailTo に送信先アドレスを、subject にメールタイトルを代入しています。

自分のメールアドレスに変更してください。

34. var mailTo = 'sample_mail@gmail.com'
35. var subject = 'テストメール'

37-44行目がメール送信部分です。

HTMLメールを送信するので、オプション部に htmlBody:'HTMLメール本文' を指定し、本文 にはHTMLが表示できない場合のテキスト文を指定しています。

こうしておけば、HTMLメールを表示できない環境の場合には、テキストメールが表示されます。

37. GmailApp.sendEmail(
38.   mailTo,
39.   subject,
40.   bodyText,
41.   {
42.     htmlBody:bodyHtml
43.   }
44. )

テキストメールを送信する

テキストメールとして送信する場合は、サンプルプログラムのオプション部を削除して下記のように変更します。

GmailApp.sendEmail(
  mailTo,
  subject,
  bodyText
)

シートの文字装飾された文字列をHTMLメールで送信する

コメントに質問を受けたのでスプレッドシートから値と文字装飾（色、太文字）を読み込んでHTMLメールを送信するサンプルプログラムを作成しました。

読み込むシートには、A1セルに赤字太文字で"象と散歩"と入力しています。

プログラムでHTMLタグを生成する

セルの文字色は、.getFontColor()で取得できます。太字かどうかは、.getFontWeight()でわかります。戻り値が、boldなら太字、太文字でなければnormalです。

次にHTMLの生成ですが、セルの文字が太文字かを判断して、太文字であれば<strong>タグで囲みます。文字色は必ずセットされているので、<p style='color:#カラーコード>で括ります。

文字色は必ずセットされるので、装飾文字のパターンは太字の有無の2パターンですが、文字色が「黒」か「他の色」かの4パターンで生成されるHTMLを下表に示します。

字幅	太字	文字色	HTML
象と散歩	なし	黒	<p style='color:#000000'>象と散歩</p>
象と散歩	なし	赤	<p style='color:#ff0000'>象と散歩</p>
象と散歩	太字	黒	<p style='color:#000000'><strong>象と散歩</strong></p>
象と散歩	太字	青	<p style='color:#0000ff'><strong>象と散歩</strong></p>

下記がサンプルコードとなります。

function sendFormattingEmail() {
  // シート名を指定
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シート1")
  // セルの内容を取得
  let cellValue = sheet.getRange("A1").getValue()
  // セルの文字色を取得
  let cellFontColor = sheet.getRange("A1").getFontColor()
  // セルの文字が太字かどうかを判定
  let cellIsBold = sheet.getRange("A1").getFontWeight() === "bold"

  Logger.log(cellValue)
  Logger.log(cellFontColor)
  Logger.log(cellIsBold)

  // 宛先メールアドレス
  let mailTo  = "sample_email@gmail.com"
  // メールの件名
  let subject = "文字色と太文字を指定したメール"

  // セルの値を取得
  let body = cellValue
  // 太字の場合はstrongタグで囲む
  if (cellIsBold) {
    body = "<strong>" + body + "</strong>"
  }
  // 文字色をstyle属性で指定
  body = "<p style='color:" + cellFontColor + "'>" + body + "</p>"

  Logger.log(body)

  // HTML形式のメールを送信
  GmailApp.sendEmail(mailTo,subject,'',{htmlBody:body})
}

上記を実行すると、セルに設定された文字装飾（文字色と太字有無）で、HTMLメールを送信することができます。（2023.7.23追記）

GASの基礎を学べる参考図書

ある程度プログラミンがわかっていれば、WEBやYoutubeでも十分に調べられると思いますが、初歩的なところからであれば参考図書は有効な学習手段です。

「詳解! Google Apps Script完全入門 [第3版]」は、プラグラミング初心者にわかりやすく説明されています。GASを最初に学ぶ一冊として良書です。

詳解! Google Apps Script完全入門 [第3版]

高橋宣成 (著)

楽天ブックスで購入す

Amazonで購入

Udemy オススメ講座

【新IDE対応】Google Apps Script（GAS）の基礎を完全習得

講師：事務職たらこ

印象に残りやすい手書き風スライドを用いGASの基本的なプログラミングを気軽に学ぶことができる。本講座でGASを活用した自動化ができるレベルにはなれないが、基礎としては十分。

Googleスプレッドシートの更新された値を知る

Googleスプレッドシートの特定セルの値が更新されたらかを知る方法についての説明です。

スプレッドシートでのプロジェクト計画管理については、スプレッドシートでプロジェクト計画を作成する方法 - Google Workspace ラーニング センター に説明があります。またこの中で更新を通知する方法として、通知を追加する でスプレッドシートの通知設定についての説明もあります。

これだけでも便利なのですが、ステータスが更新されたときなど特定のセルが更新されたときだけ通知をするなどは GoogleAppsScript での作成が必要となります。

onEdit()

GoogleAppsScriptではトリガーの設定をしなくても、特定のイベントに応じて動くシンプルトリガーという関数があります。詳細の説明は、Simple Triggers | Apps Script | Google Developers にありますが、スプレッドシートでは下記の３つの関数が使えます。

関数名	イベント
onOpen(e)	スプレッドシートを開いたとき
onEdit(e)	セルの値を変更したとき
onSelectionChange(e)	範囲選択をしたとき

onEdit を使えば、シートの値が変更されたときに実行されて、引数として変更された内容を取得することができます。慣習としてイベント時の引数として e を使っていますが、onEdit(parm) とすれば、parm を引数として使うことができます。引数で何が取得できるかの詳細は、Event Objects の Editに記載があります。

単一セルの変更なら、

• e.oldValue
• e.value

で、変更前の値、変更後の値も取得できますが、コピペで複数セルの値が変更された場合には使えません（残念）。但し、e.range で変更された場所を確認することができるので、行と列を指定して変更後の値であれば取得することができます。

変更されたシート名と変更内容を取得する

下記は、変更されたシート名と変更された範囲、変更内容を表示する簡単な例です。

sourceはスプレッドシートオブジェクトで、e.source.getActiveSheet() で変更イベントが発生したシートを取得できます。

function onEdit(e) {
  var sheet = e.source.getActiveSheet()
  console.log('シート名　 =', sheet.getName())
  console.log('変更範囲　 =', e.range)
  console.log('変更前の値 =', e.oldValue)
  console.log('変更後の値 =', e.value)
}

シート1のA1セル（空白セル）にaaaと入力してログを確認すると、下記のようにログが出力されます。

シート名 = シート1
変更範囲 = {columnEnd:1, columnStart:1, rowEnd:1, rowStart:1}
変更前の値 = undefined
変更後の値 = aaa

e.range の中身は、

• columnEnd（終了列）
• columnStart（開始列）
• rowEnd（終了行）
• rowStart（開始行）

となっています。順番が不思議な感じがしますが、全て1なので1行目1列の値が更新されたことを示しています。

※rangeを出力すると、"toString:" から "rowStart:" までの長い出力となります。次例のように必要な項目だけを指定すると出力内容を特定できます。(2022/05/05 追記)

function onEdit(e) {
  var sheet = e.source.getActiveSheet()
  console.log('シート名　 =', sheet.getName())
  console.log('変更範囲(開始行)　 =', e.range.rowStart)
  console.log('変更範囲(終了行)　 =', e.range.rowEnd)
  console.log('変更範囲(開始列)　 =', e.range.columnStart)
  console.log('変更範囲(終了列)　 =', e.range.columnEnd)
  console.log('変更前の値 =', e.oldValue)
  console.log('変更後の値 =', e.value)
}

変更前の値はないので、undefined となって、変更後の値は、入力した aaa がセットされています。

次にA1セルの値を aaa から bbb に変更すると、ログは下記のように出力されて aaa が bbb に変更されたことがわかります。

シート名　 = シート1
変更範囲　 = {columnEnd:1, columnStart:1, rowEnd:1, rowStart:1}
変更前の値 = aaa
変更後の値 = bbb

では次に B1に AAA, B2に BBB がある状態で、この値をA1-A2にコピペします。

複数セルが更新されたので、更新前も更新後の値も取得できません。変更の範囲は rowEnd が2になったので、1から2行目の1列目の値（A1,A2）が更新されたことがわかります。

シート名　 = シート1
変更範囲　 = {columnEnd:1, columnStart:1, rowEnd:2, rowStart:1}
変更前の値 = undefined
変更後の値 = undefined

【注意】シンプルトリガーは、スクリプトエディターから手動でも実行できますが、手動で実行した場合には当然ですが、引数には何も入っていません。動作を確認する場合にはシートの値を変更して確認してください。

複数範囲の変更された値を取得する

前のサンプルコードで rangeでどの範囲が変更されたかは行と列の開始と終了でわかるのでこの値を使って変更内容を表示させる例となります。

変更行と変更列で二重ループにして個々の値を取得します。

3-6行目は、変更開始/終了行、変更開始/終了列をそれぞれ取得しています。

また10行目の sheet.getRange(row, col).getValue() で指定した行と列のセルの値を取得しています。

function onEdit(e) {
  var sheet = e.source.getActiveSheet()
  var rowStart = e.range.rowStart //変更開始行
  var rowEnd = e.range.rowEnd //変更終了行
  var columnStart = e.range.columnStart //変更開始列
  var columnEnd = e.range.columnEnd //変更終了列

  //変更行の確認
  for (var row = rowStart; row <= rowEnd; row++) {
    //変更列の確認
    for (var col = columnStart; col <= columnEnd; col++) {
      console.log(row + '行' + col + '列目 =', sheet.getRange(row, col).getValue())
    }
  }
}

上の例と同じく B1に AAA, B2に BBB がある状態で、この値をA1-A2にコピペします。ログを確認すると、

1行1列目= AAA
2行1列目= BBB

A1セル（1行1列目）に AAA が、A2セル（2行1列目）がセットされたことがわかります。

onEditの限界

とってな便利なonEditですが、シンプルエティターとしての限界があります。例えば、特定のセルが更新された場合にメールを送信するなどをしようとすると、

Exception: スクリプトにはその操作を行う権限がありません。

と、メール送信の権限がないと言われてエラーとなってしまいます。

メールやチャットルールに投稿するなどをする場合には、onEdit関数ではなく、トリガーを設定する必要があります。

シート変更時に関数を実行する

トリガーを選択すると、スプレッドシートの起動時、変更時などに特定の関数を実行することができます。スプレッドシートで使えるイベントは下記の４種類です。

イベントの種類	関数が実行される条件
起動時	スプレッドシートを起動したとき
編集時	スプレッドシートを編集したとき
変更時	スプレッドシートを変更したとき
フォーム送信時	フォームを送信したとき

編集時と変更時の違い

「編集時」では、行の追加・削除、列の追加・削除を含めてスプレッドシートに何らかの変更があった場合に起動されますが、「変更時」では、セルの値が変更された場合だけ起動されます。

注意事項は、トリガーで起動した場合にパラメータで値が取得できるのは「編集時」だけ です。インベントの種類を「変更時」にすると、下記のようにエラーになってしまいますので、

TypeError: Cannot read property 'rowStart' of undefined

イベントの種類は「編集時」を選択 してください。

下記の例は、変更行と変更列の二重ループではなく、getRangeで変更された値を配列として取得する方法です。

function modifiedSells(e) {
  var sheet = e.source.getActiveSheet()

  //変更行数
  var numberOfRows = e.range.rowEnd - e.range.rowStart +1
  //変更列数
  var numberOfClums = e.range.columnEnd - e.range.columnStart+1
  //変更された値を取得
  var values = sheet.getRange(e.range.rowStart, e.range.columnStart, numberOfRows, numberOfClums).getValues()
  
  console.log(values);
}

getRangeは、

Sheetオブジェクト.getRange（開始行, 開始列, 行数, 列数）

で範囲を指定して複数の値を取得できます。

9行目で変更された行数を11行目で変更された列数を求めて、変更された値を Values に格納しています。

例えば、コピペによる複数行（２行）、複数列（2列）の値が更新された場合は、下記のように表示されます。

[ [ 'AAA', 'BBB' ], [ 'CCC', 'DDD' ] ]

最初の行の変更値は、values[0][0]、values[0][1]で（AAAとBBB）　で、

次の行の変更値が、values[0][0]、values[0][1]で（CCCとDDD）となります。

Googleフォームの選択肢をシートのデータで更新する

GAS（Google Apps Script）で Googleフォームのプルダウン、チェックボックス、ラジオボタンの選択肢をスプレッドシートの値で更新する方法についての説明です。

同じフォームを長く使っていて、時々フォーム上の選択肢を更新するような場合には、シート上で選択肢の値が管理できるようになるので便利です。

このサンプルコードでできることは、

• シート上の値で選択肢の内容を更新
• プルダウン、チェックボックス、ラジオボタンに対応
• 更新できるの質問は1個（質問番号を指定）

です。

フォームとシートの作成（準備）

プルダウン、チェックボックス、ラジオボタンの選択肢の更新をするので、下記の３つの質問を作成します。選択肢はスプレッドシートから更新するので設定不要です。

1. 商品名（プルダウン）
2. 商品名（チェックボックス）
3. 商品名（ラジオボタン）

次に選択肢をシートで管理するために、シートのA列1行目にタイトルを入れて、2行目からデータを入れます。

これで準備は完了です。

次からがGoogle Apps Scriptの例になります。

プルダウンの選択肢を更新する

質問番号を指定して質問の選択肢を更新するという簡単なサンプルです。このサンプルコードはプルダウンの質問項目の選択肢用なので、指定した質問番号がチェックボックス、ラジオボタンの場合にはエラーとなります。

上記フォーム例では、プルダウンは1番目の質問です。

下記サンプルコードをスクリプトエディタのコードに貼り付けてください。

function updateFormList() {
  //フォームの質問番号
  var questionNo = 1
  //フォームID
  var formId  = '1234567890abcdefghijklmnopqrstuvwxyz'
  //シートID
  var sheetId = 'zyxwvutsrqponmlkjihgfedcba0987654321'
  //シート名
  var sheetName = 'List'
  //データ配置列名
  var colName = 'A'
  //データ開始行
  var rowNum = 2
 
  //フォームの取得
  var form = FormApp.openById(formId)
  //全質問項目を取得
  var items = form.getItems()
  //questionNoで指定した質問項目を指定（配列は0から始まるので-1）
  var item = items[questionNo-1]

  //スプレッドシートを取得
  var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName)
  //シートの最終行を取得
  var lastRow = sheet.getLastRow()

  //リスト内容の取得
  var arrList = sheet.getRange(colName + rowNum + ':' + colName + lastRow).getValues()

  //選択肢をフォームに反映
  item.asListItem().setChoiceValues(arrList)
}

フォームの質問番号

更新する質問番号を3行目の変数（questionNo）に代入します。上記のGoogleフォームの例ではプルダウンの質問は1番目なので1を指定します。

 2.  //更新するフォームの質問番号
 3.  var questionNo = 1

実際には配列として取得されるので1番目の質問項目は、配列の0番目となるので、16行目で questionNo-1 としています。

19.  //questionNoで指定した質問項目を指定（配列は0から始まるので-1）
20.  var item = items[questionNo-1]

フォームIDとシートID

GoogleフォームとGoogleスプレッドシートは、IDで取得しています。

フォームIDについては、象と散歩: GASでGoogleフォームの値を取得する（フォームを指定） でも説明していますが、再掲となります。

Googleフォームの編集URLが下記の場合、フォームIDは、赤字の部分です。

https://docs.google.com/forms/d/1234567890abcdefghijklmnopqrstuvwxyz/edit

シートIDも同様でに赤字の部分となります。

https://docs.google.com/spreadsheets/d/zyxwvutsrqponmlkjihgfedcba0987654321/edit

5行目にシートIDを7行目にシートIDを指定していますが、作成したフォームとスプレッドシートのURLを確認して書き換えてください。

 4.  //フォームID
 5.  var formId = ‘1234567890abcdefghijklmnopqrstuvwxyz'
 6.  //シートID
 7.  var sheetId = 'zyxwvutsrqponmlkjihgfedcba0987654321'

シート名

9行目にスプレッドシートのシート名を指定します。今回のサンプルではシート名をListとしています。

 8.  // シート名
 9.  var sheetName = 'List'

データ開始位置

10-13行目でシートのデータ開始を指定します。例ではA列2行目からが選択肢のデータとなりますので、11行目の colName にAを、13行目の rowNum に2を指定します。

10.  //データ配置列名
11.  var colName = 'A'
12.  //データ開始行
13.  var rowNum = 2

プルダウン選択肢の更新

28行目で、指定したデータ開始位置（A列2行目）からデータがある最終行までを arrList に格納しています。

シート上の 商品A, 商品B, 商品C が、

arrList[0]    商品A

arrList[1]    商品B

arrList[2]    商品C

として格納されます。

そして、31行目で、この配列の値でフォームの質問番号に該当する質問の選択肢（プルダウン）を更新しています。

30. //選択肢を更新する
31. item.asListItem().setChoiceValues(arrList)

asListItem() はプルダウン項目用なので questionNo で指定した質問がプルダウンでない場合にはエラーとなるので注意してください。item.getType() で質問のタイプを確認することができます。プルダウンの質問のタイプは LIST です。

updateFormList を実行します。

ログには実行完了とだけ表示されますが、フォームを参照すると、

シート上のデータでプルダウンの選択肢が更新されています。

チェックボックスの選択肢を更新する

スプレッドシート上にある選択肢を配列に入れて、フォームの選択肢を更新する処理は基本的には同じです。プラダウンのサンプルを実行されたのであれば変更箇所は２箇所だけです。

フォームの質問番号

更新する質問番号を3行目の変数（questionNo）に代入しますが、上記のGoogleフォームの例ではチェックボッククスの質問は2番目なので2を指定します。

 2.  //更新するフォームの質問番号
 3.  var questionNo = 2

チェックボックス選択肢の更新

31行目をチェックボックス項目用の asCheckboxItem() に変更します。またチェックボックスは、選択肢に「その他」を設定できますが、showOtherOption() でその他の有無を指定します。

• showOtherOption(true)    その他 あり
• showOtherOption(false)   その他 なし

その他の選択肢を追加する場合の例となります。

30. //選択肢を更新する
31. item.asCheckboxItem( ).setChoiceValues(arrList).showOtherOption(true)

updateFormList を実行すると、商品A ,B,C と その他の選択肢が追加されます。

チェックボックスを更新するコード全体はこちらです。

function updateFormList() {
  //フォームの質問番号
  var questionNo = 2
  //フォームID
  var formId  = '1234567890abcdefghijklmnopqrstuvwxyz'
  //シートID
  var sheetId = 'zyxwvutsrqponmlkjihgfedcba0987654321'
  //シート名
  var sheetName = 'List'
  //データ配置列名
  var colName = 'A'
  //データ開始行
  var rowNum = 2
 
  //フォームの取得
  var form = FormApp.openById(formId)
  //全質問項目を取得
  var items = form.getItems()
  //questionNoで指定した質問項目を指定（配列は0から始まるので-1）
  var item = items[questionNo-1]

  //スプレッドシートを取得
  var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName);
  //シートの最終行を取得
  var lastRow = sheet.getLastRow()

  //リスト内容の取得
  var arrList = sheet.getRange(colName + rowNum + ':' + colName + lastRow).getValues()

  //選択肢をフォームに反映
  item.asCheckboxItem( ).setChoiceValues(arrList).showOtherOption(true)
}

ラジオボックスの選択肢を更新する

続いて、ラジオボックの場合となります。こちらも変更箇所は２箇所だけです。

フォームの質問番号

ラジオボックスの質問は3番目なので questionNo に 3 を代入します。

 2.  //更新するフォームの質問番号
 3.  var questionNo = 3

ラジオボックス選択肢の更新

35行目をラジオボックス用の asMultipleChoiceItem() に変更します。今回は、「その他」の選択肢を付けない showOtherOption(false) とします。

30. //選択肢を更新する
31. item.asMultipleChoiceItem().setChoiceValues(arrList).showOtherOption(false)

updateFormList を実行すると、商品A ,B,C がラジオボックの選択肢として追加されます。

ラジオボックスを更新するコード全体はこちらです。

function updateFormList() {
  //フォームの質問番号
  var questionNo = 3
  //フォームID
  var formId  = '1234567890abcdefghijklmnopqrstuvwxyz'
  //シートID
  var sheetId = 'zyxwvutsrqponmlkjihgfedcba0987654321'
  //シート名
  var sheetName = 'List'
  //データ配置列名
  var colName = 'A'
  //データ開始行
  var rowNum = 2
 
  //フォームの取得
  var form = FormApp.openById(formId)
  //全質問項目を取得
  var items = form.getItems()
  //questionNoで指定した質問項目を指定（配列は0から始まるので-1）
  var item = items[questionNo-1]

  //スプレッドシートを取得
  var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName)
  //シートの最終行を取得
  var lastRow = sheet.getLastRow()

  //リスト内容の取得
  var arrList = sheet.getRange(colName + rowNum + ':' + colName + lastRow).getValues()

  //選択肢を更新する
  item.asMultipleChoiceItem().setChoiceValues(arrList).showOtherOption(false)
}

更新する質問のタイプを確認しよう（22.2.11追記）

何件かご質問もいただきましたが、上記のサンプルで実行エラーとなる場合の多くは、質問番号で指定した番号の質問タイプと利用するメソッドが違う場合です。

下記のエラーが出力される場合が、

エラー Exception: Invalid conversion for item type: XXXXX

更新しようとする質問のタイプとメソッドが一致していない場合に出力されるエラーで、type:XXXX には、指定した質問のタイプが表示されます。

Gooleフォームで設問に指定できる項目とタイプについて下表にまとめています。

質問項目	タイプ	更新に利用するメソッド
プルダウン	LIST	asListItem()
チェックボックス	CHECKBOX	asCheckboxItem()
ラジオボタン	MULTIPLE_CHOICE	asListItem()
記述式	TEXT	-
段落	PARAGRAPH_TEXT	-
ファイルのアップロード	FILE_UPLOAD	-
均等メモリ	SCALE	-
選択式（グリッド）	GRID	-
チェックボックス（グリッド）	CHECKBOX_GRID	-
日付	DATE	-
時刻	TIME	-
セクション	PAGE_BREAK	-
タイトル	SECTION_HEADER	-
画像	IMAGE	-
動画	VIDEO	-

セクション、写真、動画なども質問となるので指定する番号に注意してください。

質問タイプを確認するコード

Loggerで指定した質問番号のタイプをログに出力するコードを追加してみてください。

19. //questionNoで指定した質問項目を指定（配列は0から始まるので-1）
20. var item = items[questionNo-1]

下記のコードを21行目以降に追加します。

  Logger.log('質問総数：' + items.length)
  Logger.log('質問番号：' + questionNo)
  Logger.log('タイトル：' + item.getTitle())
  Logger.log('タイプ：' + item.getType())

下記のように、質問総数、指定した質問番号（questionNo）、質問のタイトル、タイプが出力されます。

情報 質問総数：3
情報 質問番号：1
情報 タイトル：商品名（プルダウン）
情報 タイプ：LIST

下記は、更新はせずに、指定した質問番号のタイプを確認するサンプルコードです。

function updateFormList() {
  //フォームの質問番号
  var questionNo = 1
  //フォームID
  var formId  = '1234567890abcdefghijklmnopqrstuvwxyz'
 
  //フォームの取得
  var form = FormApp.openById(formId)
  //全質問項目を取得
  var items = form.getItems()
  //questionNoで指定した質問項目を指定（配列は0から始まるので-1）
  var item = items[questionNo-1]

  Logger.log('質問総数：' + items.length)
  Logger.log('質問番号：' + questionNo)
  Logger.log('タイトル：' + item.getTitle())
  Logger.log('タイプ：' + item.getType())
}

スプレッドシートのメニューから実行する

下記のコードを追加すると、スプレッドシートを開いたときに、メニューに "Form選択肢更新" が追加されます。

”商品名更新” を選択すると updateFormList() がスクリプト エディタを開かなくても実行できます。

function onOpen(){ 
  SpreadsheetApp
    .getActiveSpreadsheet()
    .addMenu('Form選択肢更新', [
      {name: '商品名更新', functionName: 'updateFormList'},
    ]);
}

GASの基礎を学べる参考図書

ある程度プログラミンがわかっていれば、WEBやYoutubeでも十分に調べられると思いますが、初歩的なところからであれば参考図書は有効な学習手段です。

「詳解! Google Apps Script完全入門 [第3版]」は、プラグラミング初心者にわかりやすく説明されています。GASを最初に学ぶ一冊として良書です。

詳解! Google Apps Script完全入門 [第3版]

高橋宣成 (著)

楽天ブックスで購入す

Amazonで購入

Udemy オススメ講座

【新IDE対応】Google Apps Script（GAS）の基礎を完全習得

講師：事務職たらこ

印象に残りやすい手書き風スライドを用いGASの基本的なプログラミングを気軽に学ぶことができる。本講座でGASを活用した自動化ができるレベルにはなれないが、基礎としては十分。

GoogleスプレッドシートでグラフをWEBに公開する方法

Googleスプレッドシートで作成したグラフは、画像としてダウンロードしなくても、そのままブログやサイトに掲載できるということに最近気が付きました。

Googleスプレッドシートのグラフ公開手順

手順はとても簡単です。

1. 作成したグラフをクリック
2. グラフ右上に表示される「︙」（縦三点リーダー）をクリック
3. グラフを公開 を選択

「グラフの公開」を選択すると「ウェブに公開」というページに遷移しますので「公開」をクリックします。

埋め込むタブを選択するとhtmlコード（iframe）が表示されます。

グラフは、インタラクティブか画像を選択ができます。インタラクティブを選ぶとグラフにマウスポインタを合わせるとポイントしたグラフの値が表示されます。

下記はバーチャートの表示例です。

Googleスプレッドシートのグラフを直接参照しているので、スプレッドシートでグラフが参照しているデータやグラフを変更すると公開しているグラフも連動して変更されます。またスプレッドシートを削除してしまうと表示されなくなってしまいます。

スマホの表示が崩れる

取得した埋め込みコードのままだと、グラフのサイズによってはスマホの表示が崩れてしまいます。

スタイルシートで調整しようとしましたが挫折しました。コード上のwidth（横幅） と height（高さ）を適宜調整してください。

無知は罪なりでした、、、、。